DROP TRIGGER trig_grade
GO
CREATE TRIGGER trig_grade ON enroll
AFTER INSERT
AS
BEGIN
	DECLARE @section_id INTEGER
	DECLARE @student_id VARCHAR(20)
	DECLARE @original_grade VARCHAR(10)
	DECLARE @target_grade VARCHAR(10)
	DECLARE @course_number VARCHAR(10)
	DECLARE @class_id INTEGER
	DECLARE @faculty_name VARCHAR(50)
	DECLARE @quarter VARCHAR(20)
	DECLARE @year INTEGER

	SELECT @section_id = (SELECT section_id FROM inserted)
	SELECT @student_id = (SELECT student_id FROM inserted)
	SELECT @original_grade = (SELECT grade FROM inserted)
	SELECT @target_grade = (SELECT target_grade FROM grade_merge
														WHERE original_grade = @original_grade)
	SELECT @class_id = (SELECT class_id FROM section WHERE section_id = @section_id)
	SELECT @course_number = (SELECT course_number FROM class 
													WHERE class_id = @class_id)
	SELECT @faculty_name = (SELECT faculty_name FROM section 
													WHERE section_id = @section_id)
	SELECT @quarter = (SELECT quarter FROM class 
													WHERE class_id = @class_id)
	SELECT @year = (SELECT year FROM class 
													WHERE class_id = @class_id)


IF EXISTS (SELECT grade FROM inserted)
	BEGIN
		IF EXISTS (SELECT * FROM CPQG WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND quarter = @quarter
								AND year = @year
								AND grade = @target_grade)
			BEGIN
				IF EXISTS (SELECT * FROM grade_merge WHERE original_grade = @original_grade)
					BEGIN
						UPDATE CPQG SET count_grade = count_grade + 1
								WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND quarter = @quarter
								AND year = @year
								AND grade = @target_grade
					END
				ELSE
					BEGIN
						UPDATE CPQG SET count_grade = count_grade + 1
								WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND quarter = @quarter
								AND year = @year
								AND grade = 'other'
					END	
			END
		ELSE
			BEGIN
				IF EXISTS (SELECT * FROM grade_merge WHERE original_grade = @original_grade)
					BEGIN
						INSERT INTO CPQG VALUES (@course_number, @faculty_name, @quarter, @year, @target_grade, 1)
					END
				ELSE
					BEGIN
						INSERT INTO CPQG VALUES (@course_number, @faculty_name, @quarter, @year, 'other', 1)	
					END	
			END

		IF EXISTS (SELECT * FROM CPG WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND grade = @target_grade)
			BEGIN
				IF EXISTS (SELECT * FROM grade_merge WHERE original_grade = @original_grade)
					BEGIN
						UPDATE CPG SET count_grade = count_grade + 1
								WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND grade = @target_grade
					END
				ELSE
					BEGIN
						UPDATE CPG SET count_grade = count_grade + 1
								WHERE course_number = @course_number
								AND faculty_name = @faculty_name
								AND grade = 'other'
					END	
			END
		ELSE
			BEGIN
				IF EXISTS (SELECT * FROM grade_merge WHERE original_grade = @original_grade)
					BEGIN
						INSERT INTO CPG VALUES (@course_number, @faculty_name, @target_grade, 1)
					END
				ELSE
					BEGIN
						INSERT INTO CPG VALUES (@course_number, @faculty_name, 'other', 1)	
					END	
			END
		
	END
END